import mysql.connector
from typing import List, Dict, Any, Optional, Tuple
from datetime import datetime
from decimal import Decimal

class DatabaseTool:
    def __init__(self):
        self.connection = mysql.connector.connect(
            host="localhost",
            user="root",
            password="20539",
            database="smart_supermarket",
            autocommit=False  # 禁用自动提交
        )
        self.cursor = self.connection.cursor(dictionary=True)

    def _ensure_connection(self):
        """确保数据库连接有效"""
        try:
            self.connection.ping(reconnect=True, attempts=3, delay=5)
        except mysql.connector.Error as err:
            # 重新连接
            self.connection = mysql.connector.connect(
                host="localhost",
                user="root",
                password="20539",
                database="smart_supermarket",
                autocommit=False
            )
            self.cursor = self.connection.cursor(dictionary=True)

    def _begin_transaction(self):
        """开始新的事务"""
        self._ensure_connection()
        try:
            # 如果有未完成的事务，先回滚
            self.connection.rollback()
            self.connection.start_transaction()
        except mysql.connector.Error as err:
            print(f"开始事务时出错: {err}")
            raise

    def check_stock(self, product_id: int) -> Dict[str, Any]:
        """检查商品库存"""
        query = "SELECT * FROM products WHERE id = %s"
        self.cursor.execute(query, (product_id,))
        return self.cursor.fetchone()

    def update_stock(self, product_id: int, quantity: int, change_type: str, operator: str = "系统") -> bool:
        """更新库存"""
        try:
            self._begin_transaction()

            # 获取当前库存
            current_stock = self.check_stock(product_id)
            if not current_stock:
                raise Exception("商品不存在")

            # 计算新库存
            new_stock = current_stock['stock']
            if change_type == 'In':
                new_stock += quantity
            elif change_type == 'Out':
                if new_stock < quantity:
                    raise Exception("库存不足")
                new_stock -= quantity

            # 更新products表的库存
            update_query = "UPDATE products SET stock = %s WHERE id = %s"
            self.cursor.execute(update_query, (new_stock, product_id))

            # 记录库存变动到inventory表
            inventory_query = """
            INSERT INTO inventory 
            (product_id, change_type, change_quantity, operator, change_time) 
            VALUES (%s, %s, %s, %s, %s)
            """
            current_time = datetime.now()
            self.cursor.execute(inventory_query, (
                product_id,
                change_type,
                quantity,
                operator,
                current_time
            ))

            # 如果是出库操作，创建销售记录
            if change_type == 'Out':
                # 计算总金额
                total_amount = Decimal(str(current_stock['price'])) * Decimal(str(quantity))
                
                # 创建销售记录
                sales_query = """
                INSERT INTO sales 
                (product_id, quantity, total_amount, sale_date) 
                VALUES (%s, %s, %s, %s)
                """
                self.cursor.execute(sales_query, (
                    product_id,
                    quantity,
                    float(total_amount),
                    current_time
                ))

            # 提交事务
            self.connection.commit()
            return True

        except Exception as e:
            # 发生错误时回滚
            try:
                self.connection.rollback()
            except:
                pass
            print(f"更新库存时出错: {str(e)}")
            raise e

    def get_product_by_name(self, name: str) -> Optional[Dict[str, Any]]:
        """根据商品名称查找商品"""
        query = "SELECT * FROM products WHERE name LIKE %s"
        self.cursor.execute(query, (f"%{name}%",))
        return self.cursor.fetchone()

    def get_inventory_history(self, product_id: Optional[int] = None, 
                            start_date: Optional[str] = None,
                            end_date: Optional[str] = None) -> List[Dict[str, Any]]:
        """获取库存变动历史"""
        query = """
        SELECT i.*, p.name as product_name, p.price
        FROM inventory i
        JOIN products p ON i.product_id = p.id
        WHERE 1=1
        """
        params = []

        if product_id:
            query += " AND i.product_id = %s"
            params.append(product_id)
        if start_date:
            query += " AND i.change_time >= %s"
            params.append(start_date)
        if end_date:
            query += " AND i.change_time <= %s"
            params.append(end_date)

        query += " ORDER BY i.change_time DESC"
        
        self.cursor.execute(query, tuple(params))
        return self.cursor.fetchall()

    def get_low_stock_products(self, threshold: int = 10) -> List[Dict[str, Any]]:
        """获取库存低于阈值的商品"""
        query = "SELECT * FROM products WHERE stock <= %s"
        self.cursor.execute(query, (threshold,))
        return self.cursor.fetchall()

    def process_payment(self, product_id: int, quantity: int, 
                       payment_method: str, coupon_id: Optional[int] = None) -> Tuple[bool, float]:
        """处理支付"""
        try:
            self._begin_transaction()

            # 获取商品信息
            product = self.check_stock(product_id)
            if not product:
                raise Exception("商品不存在")

            # 计算总金额
            total_amount = Decimal(str(product['price'])) * Decimal(str(quantity))

            # 如果有优惠券，应用优惠
            if coupon_id:
                coupon_query = "SELECT * FROM coupons WHERE id = %s AND status = 'active'"
                self.cursor.execute(coupon_query, (coupon_id,))
                coupon = self.cursor.fetchone()
                
                if coupon:
                    if coupon['type'] == 'percentage':
                        discount = Decimal(str(coupon['discount_value']))
                        total_amount *= (Decimal('1.0') - discount)
                    elif coupon['type'] == 'dollar' and total_amount >= Decimal(str(coupon['min_purchase_amount'])):
                        total_amount -= Decimal(str(coupon['discount_value']))

            # 更新最近的未支付销售记录
            sales_query = """
            UPDATE sales 
            SET payment_method = %s, 
                coupon_id = %s, 
                total_amount = %s,
                updated_at = %s
            WHERE product_id = %s 
            AND payment_method IS NULL
            AND sale_date = (
                SELECT max_date FROM (
                    SELECT MAX(sale_date) as max_date
                    FROM sales 
                    WHERE product_id = %s 
                    AND payment_method IS NULL
                ) as latest
            )
            """
            current_time = datetime.now()
            self.cursor.execute(sales_query, (
                payment_method,
                coupon_id,
                float(total_amount),
                current_time,
                product_id,
                product_id
            ))

            if self.cursor.rowcount == 0:
                raise Exception("没有找到待支付的订单")

            # 提交事务
            self.connection.commit()
            return True, float(total_amount)

        except Exception as e:
            # 发生错误时回滚
            try:
                self.connection.rollback()
            except:
                pass
            print(f"处理支付时出错: {str(e)}")
            raise e

    def __del__(self):
        """清理资源"""
        try:
            if hasattr(self, 'cursor'):
                self.cursor.close()
            if hasattr(self, 'connection'):
                try:
                    # 如果有未完成的事务，先回滚
                    self.connection.rollback()
                except:
                    pass
                self.connection.close()
        except:
            pass  # 忽略清理时的错误 